<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Deal
 *
 * @author bom
 */
class CmsDeal extends Zend_Db_Table_Abstract {

    //put your code here
    public function getUser($deal_id, $offset, $count) {
        $query = "SELECT a.user_id, c.fullname
            FROM cms_order as a
            LEFT JOIN cms_order_item as b ON a.order_id = b.order_id
            LEFT JOIN user_account as c ON c.user_id = a.user_id
            WHERE deal_id = $deal_id
            GROUP BY a.user_id
            LIMIT $offset,$count";
        $tb = Zend_Db_Table::getDefaultAdapter();
        $tb->setFetchMode(Zend_Db::FETCH_OBJ);
        $users = $tb->fetchAll($query);
        return $users;
    }

    public function countUser($deal_id) {
        $query = "SELECT count(DISTINCT a.user_id) as count_user
            FROM cms_order as a
            LEFT JOIN cms_order_item as b ON a.order_id = b.order_id
            WHERE deal_id = $deal_id";
        $tb = Zend_Db_Table::getDefaultAdapter();
        $tb->setFetchMode(Zend_Db::FETCH_OBJ);
        $row = $tb->fetchRow($query);
        return $row->count_user;
    }

    public function countSoldDeal($deal_id) {
        $query = "SELECT sum(quantity) as count_deal
            FROM cms_order_item
            WHERE deal_id = $deal_id";
        $tb = Zend_Db_Table::getDefaultAdapter();
        $tb->setFetchMode(Zend_Db::FETCH_OBJ);
        $row = $tb->fetchRow($query);
        return $row->count_deal;
    }
    
    public function countCompleteSoldDeal($deal_id) {
        $query = "SELECT sum(a.quantity) as count_deal
            FROM cms_order_item as a join cms_order as b on a.order_id = b.order_id
            WHERE a.deal_id = $deal_id AND b.status = 3";
        $tb = Zend_Db_Table::getDefaultAdapter();
        $tb->setFetchMode(Zend_Db::FETCH_OBJ);
        $row = $tb->fetchRow($query);
        return $row->count_deal;
    }

    public function getAllSoldDeals($page = 1, $filter = "") {
        $tb = Zend_Db_Table::getDefaultAdapter();
        $tb->setFetchMode(Zend_Db::FETCH_OBJ);

        $select = $tb->select()
                ->from(array('a'=>'cms_order_item'), array('count_deal' => 'SUM(a.quantity)'))
                ->joinRight(array('b'=>'cms_deal'),'a.deal_id = b.deal_id')
                ->where("b.deadline < now() AND (b.deal_title LIKE '%$filter%' OR b.deal_intro LIKE '%$filter%')")
                ->group('b.deal_id')
                ->order(array('deadline ASC', 'created DESC'));
        $paginator = Zend_Paginator::factory($select);
        $paginator->setItemCountPerPage(Zend_Registry::get('page_sold_deal_size'));
        $paginator->setCurrentPageNumber($page);

        return $paginator;
    }
    
    public function getSoldDeals($start, $length) {
        $tb = Zend_Db_Table::getDefaultAdapter();
        $tb->setFetchMode(Zend_Db::FETCH_OBJ);

        //$select = $tb->select()->from('cms_deal')->where('status = 3 OR deadline < now()')->order('created');
        return $tb->fetchAll($tb->select()
                ->from(array('a'=>'cms_order_item'), array('count_deal' => 'SUM(a.quantity)'))
                ->joinRight(array('b'=>'cms_deal'),'a.deal_id = b.deal_id')
                ->where('b.deadline < now()')
                ->group('b.deal_id')
                ->order(array('deadline ASC', 'created DESC'))
				->limit($length, $start));
    }
    
    public function getDealById($dealid) {
        $tb = Zend_Db_Table::getDefaultAdapter();
        $tb->setFetchMode(Zend_Db::FETCH_OBJ);
        return $tb->fetchRow("select * from cms_deal where deal_id = $dealid");
    }
}
